package com.epam.edu.dao;

import com.epam.edu.constant.Language;
import com.epam.edu.entity.OrdersMarks;
import com.epam.edu.entity.Specialty;
import com.epam.edu.entity.TeachingForm;
import com.epam.edu.entity.TeachingPlan;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class DAOSpecialty {
    public static int add(Connection connection, Specialty specialty) {
        int id = -1;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "INSERT INTO Specialties (code,name) VALUES(?,?)";
            statement = connection.prepareStatement(command, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, specialty.getCode());
            statement.setString(2, specialty.getName());
            statement.executeUpdate();
            resultSet = statement.getGeneratedKeys();
            resultSet.next();
            id = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return id;
    }

    public static void update(Connection connection, Specialty specialty) {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "UPDATE Specialties SET code=?, name=? WHERE id=?";
            statement = connection.prepareStatement(command);
            statement.setString(1, specialty.getCode());
            statement.setString(2, specialty.getName());
            statement.setInt(3, specialty.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static void delete(Connection connection, Specialty specialty) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            String command = String.format("UPDATE Specialties SET isDelete='true' WHERE id=%s", specialty.getId());
            statement = connection.createStatement();
            statement.executeUpdate(command);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static void remove(Connection connection, Specialty specialty) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            String command = String.format("DELETE Specialties WHERE id=%s", specialty.getId());
            statement = connection.createStatement();
            statement.executeUpdate(command);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public static Specialty selectById(Connection connection, int id) {
        Specialty specialty = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT code,name FROM Specialties WHERE id=? AND isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                specialty = new Specialty(id, resultSet.getString(1), resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return specialty;
    }

    public static List<Specialty> selectAll(Connection connection) {
        List<Specialty> specialties = new LinkedList<Specialty>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,code,name FROM Specialties WHERE isDelete='false'";
            statement = connection.prepareStatement(command);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Specialty specialty = new Specialty(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
                specialties.add(specialty);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return specialties;
    }

    public static Specialty selectByCode(Connection connection, String code) {
        Specialty specialty = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,code,name FROM Specialties WHERE code=? AND isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setString(1, code);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                specialty = new Specialty(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return specialty;
    }

    public static List<Specialty> selectByMarks(Connection connection, OrdersMarks ordersMarks, Language language,
                                                TeachingForm teachingForm, TeachingPlan teachingPlan) {
        List<Specialty> specialties = new LinkedList<Specialty>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT DISTINCT adm.specId FROM Admissions adm " +
                    "INNER JOIN InputMarks im ON adm.specID=im.specId " +
                    "WHERE im.historyKZ<=? " +
                    "      AND im.language<=?" +
                    "      AND im.mathematics<=?" +
                    "      AND im.subjId=?" +
                    "      AND im.mark<=?" +
                    "      AND adm.eduLanguage=?" +
                    "      AND adm.tfId=?" +
                    "      AND adm.tpId=?" +
                    "      AND adm.isDelete='false' AND im.isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, ordersMarks.getHistoryKZMark());
            statement.setInt(2, ordersMarks.getLanguageMark());
            statement.setInt(3, ordersMarks.getMathematicsMark());
            statement.setInt(4, ordersMarks.getSubject().getId());
            statement.setInt(5, ordersMarks.getMark());
            statement.setString(6, language.toString());
            statement.setInt(7, teachingForm.getId());
            statement.setInt(8, teachingPlan.getId());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Specialty specialty = selectById(connection, resultSet.getInt(1));
                specialties.add(specialty);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return specialties;
    }
}
